﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using System.Configuration;

namespace SST_DA
{
    class BaseDatos
    {
        private const string SERVER_NAME = "localhost\\SQLEXPRESS";
        private const string DEFAULT_DB = "SST";
        private const int TIMEOUT = 30;

        public static bool RevisarConexionBaseDatos() {
            try {
                using (SqlConnection sqlConn = GetConnection()) {
                    sqlConn.Open();
                    return (sqlConn.State == ConnectionState.Open);
                } 
            }
            catch (SqlException)
            {
                return false;
            }
            catch (Exception)
            {
                return false;
            }
        }//public static bool RevisarConexionBaseDatos() { 

        private static SqlConnection GetConnection() {            
            return new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["SSTConnectionString"].ConnectionString);
            /*SqlConnection conexion = new SqlConnection("user id="+usuario+";password="+contrasena+";server="+SERVER_NAME
                                                        +";database="+DEFAULT_DB+";connection timeout="+TIMEOUT);
             */
        }//private static SqlConnection GetConnection(string tipoUsuario) {

        public static DataTable ExecuteQuery(string query, string[] parametersName, object[] parameters) {

            if (!ParametersMatch(parametersName, parameters)) {
                return null;
            }

            SqlConnection conexion = GetConnection();
            SqlCommand commando = new SqlCommand(query, conexion);
            SqlDataAdapter adapter = new SqlDataAdapter(commando);
            DataTable resultado = new DataTable();

            if(parameters != null){
                int parametersCount = parametersName.Length;
                for (int i = 0; i < parametersCount; i++)
                    commando.Parameters.Add(new SqlParameter(parametersName[i], parameters[i]));
            }
            try
            {
                conexion.Open();
                adapter.Fill(resultado);
            }
            catch (SqlException e)
            {
                Console.Out.WriteLine(e.Message);
            }
            finally
            {
                conexion.Close();
            }

            return resultado;
        }//public static DataTable ExecuteQuery() { 


        public static int ExecuteNonQuery(string query, string[] parametersName, object[] parameters)
        {

            if (!ParametersMatch(parametersName, parameters))
            {
                return -1;
            }

            SqlConnection conexion = GetConnection();
            SqlCommand commando = new SqlCommand(query, conexion);
            SqlDataAdapter adapter = new SqlDataAdapter(commando);
            DataTable resultado = new DataTable();
            int AffectedRows = 0;

            if (parameters != null)
            {
                int parametersCount = parametersName.Length;
                for (int i = 0; i < parametersCount; i++)
                    commando.Parameters.Add(new SqlParameter(parametersName[i], parameters[i]));
            }
            try
            {
                conexion.Open();
                AffectedRows += commando.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.Out.WriteLine(e.Message);
            }
            finally
            {
                conexion.Close();
            }

            return AffectedRows;
        }//public static DataTable ExecuteQuery() { 

        public static bool ParametersMatch(string[] parametersNames, object[] parameters) {
            bool ParametersMatch = true;
            if (parametersNames == null || parametersNames == null) {
                ParametersMatch = false;
            }
            else if (parameters.Length != parametersNames.Length) {
                ParametersMatch = false;
            }
            return ParametersMatch;
        }//public static bool ParametersMatch(string[] parametersNames, object[] parameters) { 

    }
}
